package connection;

import shopping.Goods;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

public class GoodsDAO {

	// 获取所以商品的信息
	public ArrayList<Goods> getAllGoods() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		ArrayList<Goods> list = new ArrayList<Goods>(); // 所有商品的集合
		try {
			conn = DBConnection.getConnection();
			String sql = "select * from goods;";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				Goods good = new Goods();
				good.setNum(rs.getInt("num"));
				good.setName(rs.getString("name"));
				good.setPrice(rs.getInt("price"));
				good.setPicture(rs.getString("picture"));
				list.add(good);// 把商品加入到集合中
			}
			return list; // 返回集合
		} catch (Exception ex) {
			ex.printStackTrace();
			return null;
		} finally {

			// 关闭数据集对象
			if (rs != null) {
				try {
					rs.close();
					rs = null;
				} catch (Exception ex) {
					ex.printStackTrace();
				}
			}

			// 关闭PreparedStatement对象
			if (ps != null) {
				try {
					ps.close();
					ps = null;
				} catch (Exception ex) {
					ex.printStackTrace();
				}
			}
		}

	}

	// 根据商品编号获得商品资料
	public Goods getItByNum(int num) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = DBConnection.getConnection();
			String sql = "select * from goods where num=?;"; // SQL语句
			ps = conn.prepareStatement(sql);
			ps.setInt(1, num);
			rs = ps.executeQuery();
			if (rs.next()) {
				Goods good = new Goods();
				good.setNum(rs.getInt("num"));
				good.setName(rs.getString("name"));
				good.setPrice(rs.getInt("price"));
				good.setPicture(rs.getString("picture"));
				return good;
			} else {
				return null;
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			return null;
		} finally {

			// 关闭数据集对象
			if (rs != null) {
				try {
					rs.close();
					rs = null;
				} catch (Exception ex) {
					ex.printStackTrace();
				}
			}

			// 关闭PreparedStatement对象
			if (ps != null) {
				try {
					ps.close();
					ps = null;
				} catch (Exception ex) {
					ex.printStackTrace();
				}
			}

		}
	}
}
